import requests
import json
import sqlite3

# 创建一个SQLite数据库连接
conn = sqlite3.connect('my_database.db')

cursor = conn.cursor()


# 定义数据库表结构
cursor.execute('''CREATE TABLE IF NOT EXISTS my_table (
                    id INTEGER PRIMARY KEY,
                    code TEXT,
                    name TEXT,
                    latest_price REAL,
                    change_percent REAL,
                    change_amount REAL,
                    volume INTEGER,
                    turnover REAL,
                    amplitude REAL,
                    high REAL,
                    low REAL,
                    opening_price REAL,
                    yesterday_close REAL,
                    volume_ratio REAL,
                    turnover_rate REAL,
                    pe_ratio REAL,
                    pb_ratio REAL
                )''')

headers = {
    'user-agent' :'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36 Edg/117.0.2045.47',
    'cookie' : 'qgqp_b_id=a7c5d47be8ad882fee56fc695bab498d; st_si=17803153105309; st_asi=delete; HAList=ty-0-300045-%u534E%u529B%u521B%u901A; st_pvi=56620321867111; st_sp=2023-10-07%2015%3A19%3A51; st_inirUrl=https%3A%2F%2Fwww.eastmoney.com%2F; st_sn=52; st_psi=20231007155656228-113200301321-9129123788'
}

keypage = input("请输入要搜索的特定页面（用空格分隔）：")
searchlist = list(map(int, keypage.split()))

for page in searchlist:
    response = requests.get(url=f'http://76.push2.eastmoney.com/api/qt/clist/get?cb=jQuery112405166990298085778_1696666115151&pn={page}&pz=20&po=1&np=1&ut=bd1d9ddb04089700cf9c27f6f7426281&fltt=2&invt=2&wbp2u=|0|0|0|web&fid=f3&fs=m:0+t:6,m:0+t:80,m:1+t:2,m:1+t:23,m:0+t:81+s:2048&fields=f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f12,f13,f14,f15,f16,f17,f18,f20,f21,f23,f24,f25,f22,f11,f62,f128,f136,f115,f152&_=1696666115152',headers=headers)
    data = response.text
    start = response.text.find('(')
    end = response.text.rfind(')')
    data = response.text[start + 1:end]
    data = json.loads(data)
    data = data['data']['diff']

    plist = ['f12','f14','f2','f3','f4','f5','f6','f7','f15','f16','f17','f18','f10','f8','f9','f23']
    for i in range(len(data)):
        row = (i + 1,) + tuple(data[i][j] for j in plist)
        cursor.execute('INSERT INTO my_table VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)', row)
    conn.commit()  # 提交更改并关闭连接
    conn.close()

    print("{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}{:<10}".format("序号","代码","名称","最新价","涨跌幅","涨跌额","成交量","成交额","振幅","最高","最低","今开","昨收","量比","换手率","市盈率","市净率"))
    for i in range(len(data)):
        slist = []
        print("{:<10}".format(i+1),end="")
        for j in plist:

            slist.append(data[i][j])
        for k in slist:
            print("{:<10}".format(k),end="")
        print()